# -*- coding: utf-8 -*-

'''
/**
 * @addtogroup EasyXlsx
 * EasyXlsx
 * @{
 */
/**
 * @file        EasyXlsx.py
 * @brief       XXXX
 * @note        XXXX
 * @author      靳普诏(puzhao.jin@hopechart.com)
 * @date        2023/06/05
 * @version     1.0

 * @par         修改日志
 * <table>
 * <tr><th>Date         <th>Version     <th>Author      <th> Description
 * <tr><td>2023/06/05   <td>1.0         <td>靳普诏       <td> 创建初始版本
 * @copyright   xxxx
 */
'''

import sys
import os

'''
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 读取Excel操作 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'''
import enum
import openpyxl
from openpyxl import Workbook  # 导入新建excel文件的模块
from openpyxl import load_workbook  # 导入读取excel文件的模块


class TXlsxError(Exception):
    def __init__(self, msg="未知错误"):
        self.msg = msg

    def __str__(self):
        return repr("[TXlsxError] --> " + self.msg)

class TXlsxScanDire(enum.Enum):
    kToRight = 0
    kToDown = 1
    kToLeft = 2
    kToUp = 3

class TXlsx:
    # 初始化工作空间，传入excel文件路径
    file_path = ''

    def __init__(self, xl_path=''):
        try:  # 读取 excel
            self._xl_hander = load_workbook(xl_path, data_only=True)
            self.file_path = xl_path
            print(self._xl_hander.sheetnames)
        except FileNotFoundError:
            print("[{0}] not found".format(xl_path))
            raise

    def sheetNameArray(self):
        return self._xl_hander.sheetnames

    def sheet(self, sheet=''):
        if sheet not in self._xl_hander.sheetnames:
            # print(f"<{self.file_path} Error> Sheet:{sheet} does not exist")
            raise TXlsxError(f"<{self.file_path} Error> Sheet:_ {sheet} _ does not exist")
        return self._xl_hander[sheet]

    def sheetACell(self, sheet='', row=0, col=0):  # 0 会报错
        sh = self.sheet(sheet)
        return sh.cell(row=row, column=col)
    
    def sheetCellValue(self, sheet='', row=0, col=0):
        sh = self.sheet(sheet)
        return sh.cell(row=row, column=col).value

    def sheetAColumn(self, sheet='', col=0, start_row=1, count=1):
        result = []
        sh = self.sheet(sheet)

        if count >= 1 and start_row >= 1 and col >= 1:
            cnt = 0
            while cnt < count:
                val = sh.cell(start_row + cnt, col).value
                # if val == None:
                #     val = ''
                result.append(val)
                cnt += 1

        return result

    def sheetARow(self, sheet='', row=0, start_col=1, count=1):
        result = []
        sh = self.sheet(sheet)

        if count >= 1 and start_col >= 1 and row >= 1:
            cnt = 0
            while cnt < count:
                val = sh.cell(row, start_col + cnt).value
                # if val == None:
                #     val = ''
                result.append(val)
                cnt += 1

        return result

    # 从规定位置向单个方向扫描连续的单元格
    def sheetOneDimeScan(self, sheet='', cell_from='', row_pos=1, col_pos=1, direction=TXlsxScanDire.kToRight, end_flag=None, tag_count=-1):
        '''
        从规定位置向单个方向扫描连续的单元格
        :param sheet: 表格名称
        :param cell_from: 起始单元格。可以写入''，直接指定坐标
        :param row_pos: 当cell_from=''时，生效。起始单元格行号
        :param col_pos: 当cell_from=''时，生效。起始单元格列号
        :param direction: 扫描方向
        :param end_flag: 结束标志
        :param tag_count: 最多扫描单元格个数，-1表示无上限
        :return: 扫描得到的单元格值列表
        '''
        result = []
        sh = self.sheet(sheet)

        if cell_from != '':
            cell = sh[cell_from]
            (row_pos, col_pos) = self.transRowColmun(cell_from)
        else:
            cell = sh.cell(row=row_pos, column=col_pos)

        while (cell.value != end_flag) and (tag_count < 0 or tag_count > len(result)):
            result.append(cell.value)

            if direction == TXlsxScanDire.kToRight:
                col_pos += 1
            elif direction == TXlsxScanDire.kToLeft:
                col_pos -= 1
                if col_pos == 0:
                    break
            elif direction == TXlsxScanDire.kToUp:
                row_pos -= 1
                if row_pos == 0:
                    break
            else: # direction == TXlsxScanDire.kToDown:
                row_pos += 1

            cell = sh.cell(row=row_pos, column=col_pos)

        return result

    ## 转换获取对应横纵坐标
    def transRowColmun(self, cell_prompt='A1'):
        # 实现坐标转换 后期换成手动计算
        row = 0
        column = 0
        col_flag = True

        for letter in cell_prompt:
            asc_val = ord(letter.upper())

            if col_flag:  # 转换列号
                if asc_val >= ord("A") and asc_val <= ord("Z"):
                    val = asc_val - ord("A") + 1
                    row = row * 26 + val
                elif asc_val >= ord("1") and asc_val <= ord("9"):
                    column = asc_val - ord("0")
                    col_flag = False
                else:
                    row = column = 0
                    break

            else:  # 转换行号
                if asc_val >= ord("0") and asc_val <= ord("9"):
                    val = asc_val - ord("0")
                    column = column * 10 + val
                    col_flag = False
                else:
                    row = column = 0
                    break

        return (row, column)

    ## 在某一行列查找指定单元格
    def findCell(self, tag_val: str, sheet='', cell_from='A1', direction=TXlsxScanDire.kToRight, tag_count=-1):
        row = 0
        column = 0
        sh = self.sheet(sheet)
        cell = sh[cell_from]
        (row_pos, col_pos) = self.transRowColmun(cell_from)

        check_times = 0
        while tag_count < 0 or tag_count > check_times:

            if cell.value == tag_val:
                row = row_pos
                column = col_pos
                break
            else:
                check_times += 1

            if direction == TXlsxScanDire.kToRight:
                col_pos += 1
            elif direction == TXlsxScanDire.kToLeft:
                col_pos -= 1
                if col_pos == 0:
                    break
            elif direction == TXlsxScanDire.kToUp:
                row_pos -= 1
                if row_pos == 0:
                    break
            else: # direction == TXlsxScanDire.kToDown:
                row_pos += 1

            cell = sh.cell(row=row_pos, column=col_pos)

        return (row, column)

    ## sheet中全部内容转换成字典列表，键名向量方向向右
    def sheetToDictListByRow(self, sheet:str, title_row_index = 1, start_col = 1):
        result = []
        content = []
        dict = {}

        # 采集标题头
        key_list = self.sheetOneDimeScan(sheet, row_pos=title_row_index, col_pos=start_col, direction=TXlsxScanDire.kToRight)
        key_cnt = key_list.__len__()

        # 采集内容
        if key_cnt > 0:
            row_idx = title_row_index + 1
            while True:
                content = self.sheetARow(sheet, row_idx, start_col, key_cnt)

                if content.count(None) == key_cnt:    # 一行内容全空
                    break   
                else:   # 有内容
                    dict = {}
                    for i in range(key_cnt):
                        dict[key_list[i]] = content[i]
                    result.append(dict)
                    row_idx += 1
        else:
            result.append(dict)

        return  result

    ## sheet中全部内容转换成字典列表，键名向量方向向下
    def sheetToDictListByColumn(self, sheet:str, title_column_index = 1, start_row = 1):
        result = []
        content = []
        dict = {}

        # 采集标题头
        key_list = self.sheetOneDimeScan(sheet, row_pos=start_row, col_pos=title_column_index, direction=TXlsxScanDire.kToDown)
        key_cnt = key_list.__len__()

        # 采集内容
        if key_cnt > 0:
            column_idx = title_column_index + 1
            while True:
                content = self.sheetAColumn(sheet, column_idx, start_row, key_cnt)

                if content.count(None) == key_cnt:    # 内容全空
                    break   
                else:   # 有内容
                    dict = {}
                    for i in range(key_cnt):
                        dict[key_list[i]] = content[i]
                    result.append(dict)
                    column_idx += 1
        else:
            result.append(dict)

        return  result






''' 
 获取参数列表。键名向量方向向右
 @param xlsx_name   表格文件名
 @param sheet_name  表格sheet名
 @param title_row_index  标题行索引
 @param start_col  起始列索引
 @return 参数列表
 @note:xxxxx
'''
def ReadExcelParamListByRow(xlsx_name:str, sheet_name:str, title_row_index = 1, start_col = 1) -> list :
    # 打开Excel文件
    xlsx_file = TXlsx(xlsx_name)
    
    # 获取所有单元格内容
    param_list = xlsx_file.sheetToDictListByRow(sheet_name, title_row_index=title_row_index, start_col=start_col)

    return param_list


''' 
 获取参数列表。键名向量方向向下
 @param xlsx_name   表格文件名
 @param sheet_name  表格sheet名
 @param title_column_index  标题列索引
 @param start_row  起始行索引
 @return 参数列表
 @note:xxxxx
'''
def ReadExcelParamListByColumn(xlsx_name:str, sheet_name:str, title_column_index = 1, start_row = 1) -> list :
    # 打开Excel文件
    xlsx_file = TXlsx(xlsx_name)
    
    # 获取所有单元格内容
    param_list = xlsx_file.sheetToDictListByColumn(sheet_name, title_column_index=title_column_index, start_row=start_row)

    return param_list




if __name__ == "__main__":
    ## 获取当前脚本所在的目录
    SCRIPT_DIR = os.path.dirname(os.path.abspath(__file__))
    # 输出目录路径
    print("SCRIPT_DIR: "+ SCRIPT_DIR)

    ## 获取脚本传参
    argv = sys.argv
    argc = len(argv)
    # 打印命令行参数
    #for i in range(1, argc):
        #print("参数", i, ":", argv[i])
        
    print("============= BIGEN: EasyXlsx.py =============")

    
    test = TXlsx(f"{SCRIPT_DIR}/ref/test.xlsx")

    d = test.sheetToDictListByColumn("Sheet1")
    print(d)

    d = test.sheetToDictListByRow("Sheet1")
    print(d)

    print("============= END: EasyXlsx.py =============")



## Generated on "2023-06-05 17:09:33" by the tool "gen_hq_file.py >> V20230509_1" 